﻿Public Class frmInThongKeSoLuongTon

    Private Sub frmInThongKeSoLuongTon_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.WindowState = FormWindowState.Maximized
        Dim dbConn As New OleDb.OleDbConnection
        Dim dataAccess As New DataAccess
        Dim query As String = ""
        Dim adapter As OleDb.OleDbDataAdapter
        dataAccess.ConnectDatabase_DBAccess(dbConn)
        Dim dtSoLuongTonKho2Column As DataTable = Create_SoLuongTonKho2Column()
        'grouping
        Dim dtMaLoai As New DataTable
        If frmThongKeSoLuongTon.cbxLoaiSanPham.SelectedValue = 0 Then
            query = "Select Distinct IDLoai,MaLoai,TenLoai From LoaiSanPham Order By MaLoai ASC"
        Else
            query = "Select Distinct IDLoai,MaLoai,TenLoai From LoaiSanPham Where IDLoai=" & frmThongKeSoLuongTon.cbxLoaiSanPham.SelectedValue & " Order By MaLoai ASC"
        End If

        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        adapter.Fill(dtMaLoai)
        For j As Integer = 0 To dtMaLoai.Rows.Count - 1
            'grouping detail
            Dim dtSoLuongTonKho As New DataTable
            If frmThongKeSoLuongTon.txtTenSanPhamSearch.Text = "" Then
                query = "Select LoaiSanPham.IdLoai,TenLoai,IDSanPham,MaSanPham,TenSanPham,SoLuongTon From SanPham Left Outer Join LoaiSanPham On SanPham.IDLoai=LoaiSanPham.IDLoai Where SanPham.TheoDoiTon=1 And SanPham.Xoa=0 And SanPham.DVTSanPhamId IS NOT NULL And LoaiSanPham.IdLoai=" & dtMaLoai.Rows(j)("IdLoai") & " Order By MaSanPham ASC"
            Else
                query = "Select LoaiSanPham.IdLoai,TenLoai,IDSanPham,MaSanPham,TenSanPham,SoLuongTon From SanPham Left Outer Join LoaiSanPham On SanPham.IDLoai=LoaiSanPham.IDLoai Where SanPham.TheoDoiTon=1 And SanPham.Xoa=0 And LoaiSanPham.IdLoai=" & dtMaLoai.Rows(j)("IdLoai") & " And SanPham.DVTSanPhamId IS NOT NULL And SanPham.Xoa=0 And MaSanPham Like N'%" & frmThongKeSoLuongTon.txtTenSanPhamSearch.Text & "%' Order By MaSanPham ASC"
            End If
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtSoLuongTonKho)
            Dim i As Integer = 0
            Dim ToTal As Integer = dtSoLuongTonKho.Rows.Count - 1
            Dim Part1 As Integer = ToTal / 2
            While i <= Part1
                If (i + Part1 + 1) > dtSoLuongTonKho.Rows.Count - 1 Then
                    Dim newRow1 As DataRow
                    newRow1 = dtSoLuongTonKho2Column.NewRow
                    newRow1.Item("MaSanPham") = dtSoLuongTonKho.Rows(i)("MaSanPham")
                    newRow1.Item("SoLuongTon") = Format(dtSoLuongTonKho.Rows(i)("SoLuongTon"), "0.00")
                    dtSoLuongTonKho2Column.Rows.Add(newRow1)
                Else
                    Dim newRow2 As DataRow
                    newRow2 = dtSoLuongTonKho2Column.NewRow
                    newRow2.Item("MaSanPham") = dtSoLuongTonKho.Rows(i)("MaSanPham")
                    newRow2.Item("SoLuongTon") = Format(dtSoLuongTonKho.Rows(i)("SoLuongTon"), "0.00")
                    newRow2.Item("MaSanPham1") = dtSoLuongTonKho.Rows(i + +Part1 + 1)("MaSanPham")
                    newRow2.Item("SoLuongTon1") = Format(dtSoLuongTonKho.Rows(i + Part1 + 1)("SoLuongTon"), "0.00")
                    dtSoLuongTonKho2Column.Rows.Add(newRow2)
                End If
                i = i + 1
            End While
            'While i <= dtSoLuongTonKho.Rows.Count - 1
            '    If (i + 1) > dtSoLuongTonKho.Rows.Count - 1 Then
            '        Dim newRow1 As DataRow
            '        newRow1 = dtSoLuongTonKho2Column.NewRow
            '        newRow1.Item("MaSanPham") = dtSoLuongTonKho.Rows(i)("MaSanPham")
            '        newRow1.Item("SoLuongTon") = Format(dtSoLuongTonKho.Rows(i)("SoLuongTon"), "0.00")
            '        dtSoLuongTonKho2Column.Rows.Add(newRow1)
            '    Else
            '        Dim newRow2 As DataRow
            '        newRow2 = dtSoLuongTonKho2Column.NewRow
            '        newRow2.Item("MaSanPham") = dtSoLuongTonKho.Rows(i)("MaSanPham")
            '        newRow2.Item("SoLuongTon") = Format(dtSoLuongTonKho.Rows(i)("SoLuongTon"), "0.00")
            '        newRow2.Item("MaSanPham1") = dtSoLuongTonKho.Rows(i + 1)("MaSanPham")
            '        newRow2.Item("SoLuongTon1") = Format(dtSoLuongTonKho.Rows(i + 1)("SoLuongTon"), "0.00")
            '        dtSoLuongTonKho2Column.Rows.Add(newRow2)
            '    End If
            '    i = i + 2
            'End While
        Next
        dbConn.Close()
        Dim objReport As New rptTonKho
        objReport.Database.Tables("dtTonKho").SetDataSource(dtSoLuongTonKho2Column)
        'title report
        Dim dtTitle As New DataTable
        dtTitle.Columns.Add("Title")
        Dim newRow As DataRow
        newRow = dtTitle.NewRow
        Dim Ngay As String
        Ngay = Now.Date.ToShortDateString
        formatDate2VN(Ngay)
        newRow("Title") = "Ngày " & Ngay
        dtTitle.Rows.Add(newRow)
        dtTitle.AcceptChanges()
        objReport.Database.Tables("dtTitleReport").SetDataSource(dtTitle)
        cvrThongKe.ReportSource = objReport
    End Sub
    Protected Function Create_SoLuongTonKho2Column() As DataTable
        Dim dtTable As DataTable = New DataTable()
        Try
            Dim MaSanPham As DataColumn = New DataColumn("MaSanPham")
            MaSanPham.DataType = System.Type.GetType("System.String")
            MaSanPham.DefaultValue = ""
            dtTable.Columns.Add(MaSanPham)

            Dim SoLuongTon As DataColumn = New DataColumn("SoLuongTon")
            SoLuongTon.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(SoLuongTon)

            Dim MaSanPham1 As DataColumn = New DataColumn("MaSanPham1")
            MaSanPham.DataType = System.Type.GetType("System.String")
            MaSanPham.DefaultValue = ""
            dtTable.Columns.Add(MaSanPham1)
            Dim SoLuongTon1 As DataColumn = New DataColumn("SoLuongTon1")
            SoLuongTon1.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(SoLuongTon1)
            Return dtTable
        Catch ex As Exception
            Throw
        End Try
    End Function

End Class